package com.gopay.scheduler.service.cacert.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.gopay.common.constants.cust.CustCaCertConstants;
import com.gopay.common.constants.cust.CustPersonInfoConstants;
import com.gopay.common.constants.mail.MailLevel;
import com.gopay.common.constants.proccode.ProcCodeConstants;
import com.gopay.common.cps.dao.wallet.base.WaCertificateAllocationDao;
import com.gopay.common.cps.dao.wallet.base.WaCertificateDao;
import com.gopay.common.cps.dao.wallet.cert.CustMobCaBuyDtlDao;
import com.gopay.common.domain.cps.transfer.Trans00605VO;
import com.gopay.common.domain.cps.vo.CPSResponse;
import com.gopay.common.domain.cust.CustCorpInfo;
import com.gopay.common.domain.mail.Send;
import com.gopay.common.exception.GopayException;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.user.dao.CustCorpInfoQueryDAO;
import com.gopay.common.util.DateUtils;
import com.gopay.cron.web.util.CallCPSUtil;
import com.gopay.cron.web.util.RemoteServiceUtil;
import com.gopay.cron.web.vo.DtCertDtlConfVO;
import com.gopay.remote.cps.mail.MailRemoteService;
import com.gopay.scheduler.job.vo.MobCertRemendVO;
import com.gopay.scheduler.service.cacert.CustMobCaCertService;
import com.gopay.sms.client.GopaySMSHttpClient;
import com.gopay.sms.common.constants.SMSAppId;
import com.gopay.sms.common.domain.RequestVO;
import com.gopay.sms.common.domain.ResponseVO;

import net.sf.json.JSONObject;

/**
 * @ClassName: CustMobCaCertServiceImpl 
 * @Description: 移动数字证书回收及退款
 * @author liuxuyang
 * @date 2017年12月7日 下午7:20:21  
 */

@Service("custMobCaCertService")
public class CustMobCaCertServiceImpl implements CustMobCaCertService {

	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	private @Value("#{webProperties['remotenotify.cps.mail.url']}") String notifyEmailUrl;
	private @Value("#{webProperties['portal.url']}")
    String portalUrl; 
	
	@Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
	
	@Resource(name = "waCertificateAllocationDao")
    private WaCertificateAllocationDao waCertificateAllocationDao;
    
    @Resource(name = "custMobCaBuyDtlDao")
    private CustMobCaBuyDtlDao custMobCaBuyDtlDao;
    
    @Resource(name="custCorpInfoQueryDao")
    private CustCorpInfoQueryDAO custCorpInfoQueryDAO;
    
    @Resource(name = "waCertificateDao")
    private WaCertificateDao waCertificateDao;
	
    @Override
	public void recoveryEnpiredCert() {
		List<DtCertDtlConfVO> dtCertDtlConfs = queryExpireCertOptimize();
		if (null == dtCertDtlConfs) {
			logger.info("没有可回收的移动数字证书");
			return;
		}
		int size = dtCertDtlConfs.size();
		logger.info("查询到待回收的数字证书列表长度:{}" , size);
		
		if (size > 0) {
			for (DtCertDtlConfVO dtCertDtlConfVO : dtCertDtlConfs) {
				Long buyDtlId = dtCertDtlConfVO.getBuyDtlid();
				if(CustCaCertConstants.FREE_CA_CERT.equals(dtCertDtlConfVO.getDtlType())){
					try {
						int drawBackNum = dtCertDtlConfVO.getUnusedCount() + waCertificateAllocationDao.getUnUsedAllocate(buyDtlId);
					    //更新购买流水
					    updateFreeCert(buyDtlId,drawBackNum,dtCertDtlConfVO.getAuditStatus(),dtCertDtlConfVO.getCustId());
					} catch (Exception e) {
						logger.error("回收赠送的移动数字证书失败,buyDtlId:" + buyDtlId,e);
					}
				    
				} else if(CustCaCertConstants.PURCHASE_CA_CERT.equals(dtCertDtlConfVO.getDtlType())){
				    logger.info("付费购买证书调用cps回收：dtlID {}" , buyDtlId);

		            Trans00605VO transVo = buildTrans00605VO(buyDtlId);
		            CPSResponse response = null;
		            try {
		                response = CallCPSUtil.callByVOTicket(transVo);
		                if (!ProcCodeConstants.PROC_CODE_100S1000.equals(response.getRespCode())) {
		                    logger.info("回收付费购买证书退款走账异常");
		                    throw new GopayException(response.getRespCode());
		                }else{
		                    String orderId = (String)response.getRespData().get("gopayOrderId");
							logger.info("回收付费购买证书额度退款成功: orderId {} " , orderId);
		                }
		            } catch (GopayException e) {
		                logger.error(e.getErrCode()+":"+e.getErrMsg());
		            }
				}
			}
		}
	}
    
	/**
	 * @Description: 查询出所有待退款证书交易
	 * @return 
	 * @author liuxuyang
	 */
	private List<DtCertDtlConfVO> queryExpireCertOptimize() {
		Map<String,Object> param = new HashMap<String,Object>(16);
		StringBuffer sql = new StringBuffer();
		
		sql.append("select t.id as buyDtlId, ")
		.append("t.unused_count,")
		.append("t.dtl_type, ")
		.append("t.audit_stat, ")
		.append("t.install_expire_ts, ")
		.append("t.cust_id ")
		.append("from CUST_MOB_CA_BUY_DTL t left join WA_CERTIFICATE_ALLOCATION r on t.id = r.cert_buy_id ")
		.append("where t.unused_count = 0 ")
		.append("and r.allocation_status = :allocation_status ")
		.append("and t.install_expire_ts < sysdate ")
		.append("group by t.id,t.unused_count, t.dtl_type, t.audit_stat, t.install_expire_ts,t.cust_id ")
		.append("union all ")
		.append("select t.id as buyDtlId, ")
		.append("t.unused_count,")
		.append("t.dtl_type, ")
		.append("t.audit_stat, ")
		.append("t.install_expire_ts, ")
		.append("t.cust_id ")
		.append("from CUST_MOB_CA_BUY_DTL t  ")
		.append("where t.unused_count > 0 ")
		.append("and t.install_expire_ts < sysdate ");
		
		param.put("allocation_status", "02");
		
		List<Object[]> list = commonQueryDAO.findBySql(sql.toString(), param);
		
		return packageList(list);
	}
	
	/**
	 * @Description: 更新购买流水和分配表
	 * @param buyDtlId
	 * @param drawBackNum 
	 * @author liuxuyang
	 */
	@Transactional(propagation=Propagation.REQUIRED,rollbackFor=Exception.class)
	public void updateFreeCert(long buyDtlId,int drawBackNum,String auditStatus,String custId) {
		logger.info("购买流水号 buyDtlId:{},回收数量  drawBackNum:{}" ,buyDtlId,drawBackNum);
		
		String hql = "update  CustMobCaBuyDtl set drawbackCaCount=:drawbackCaCount  "
				+ " ,unusedCount =:unusedCount "
				+ " where id = :id ";
		Map<String, Object> param = new HashMap<String, Object>(16);
		param.put("drawbackCaCount", drawBackNum);
		param.put("unusedCount", 0);
		param.put("id", buyDtlId);
		//更新购买流水表
		custMobCaBuyDtlDao.updateByHql(hql, param);
		
		//更新安装表
		waCertificateDao.updateCertApplyStatus(buyDtlId);
		//批量插入申请数据
    	waCertificateDao.batchInsertApplyCert(buyDtlId);
		
    	String hqlConf = "update  WaCertificateAllocation set allocationStatus=:allocationStatus  "
				+ " where certBuyId =:certBuyId and allocationStatus = :status";
		Map<String, Object> paramConf = new HashMap<String, Object>(16);
		paramConf.put("allocationStatus", "04");
		paramConf.put("status", "02");
		paramConf.put("certBuyId", buyDtlId);
		//更新分配表
		waCertificateAllocationDao.updateByHql(hqlConf, paramConf);
		
		String hqlConfUpdate = "update  WaCertificateAllocation set allocationStatus=:allocationStatus,isUpdate='0'  "
				+ " where certBuyId =:certBuyId and allocationStatus = :status and isUpdate ='1' ";
		Map<String, Object> paramConfUpdate = new HashMap<String, Object>(16);
		paramConfUpdate.put("allocationStatus", "03");
		paramConfUpdate.put("status", "04");
		paramConfUpdate.put("certBuyId", buyDtlId);
		//更新分配表
		waCertificateAllocationDao.updateByHql(hqlConfUpdate, paramConfUpdate);
		
		//关闭证书服务
		/*if (StringUtils.equals(auditStatus, "")) {
	    	CustCorpInfo custCorpInfo =custCorpInfoQueryDAO.getByCustId(custId);
	    	custCorpInfo.setMobCaCertSwitch(CustPersonInfoConstants.MOB_CA_CERT_SWITCH_OFF);
	    	custCorpInfo.setLastUpdTs(new Date());
	    	custCorpInfoQueryDAO.update(custCorpInfo);
	    }*/
	}
	
	/**
	 * @Description: 封装查询结果 
	 * @param list
	 * @return 
	 * @author liuxuyang
	 */
	private List<DtCertDtlConfVO> packageList(List<Object[]> list) {
		if(list == null || list.size() == 0) {
			return null;
		}
		List<DtCertDtlConfVO> dtCertDtlConfs = new ArrayList<DtCertDtlConfVO>();
		
		for (Object[] bean : list) {
			DtCertDtlConfVO dtCertDtlConfVO = new DtCertDtlConfVO();
			dtCertDtlConfVO.setBuyDtlid(Long.parseLong(bean[0].toString()));
			dtCertDtlConfVO.setUnusedCount(Integer.parseInt(bean[1].toString()));
			dtCertDtlConfVO.setDtlType(bean[2].toString());
			dtCertDtlConfVO.setAuditStatus(bean[3] == null ?"":bean[3].toString());
			dtCertDtlConfVO.setInstallExpireTs((Date)bean[4]);
			dtCertDtlConfVO.setCustId(bean[5].toString());
			dtCertDtlConfs.add(dtCertDtlConfVO);
		}
		
		return dtCertDtlConfs;
	}
	
	/**
	 * 
	 * @Description: 组建00605退款交易VO 
	 * @param buyDtlId
	 * @return 
	 * @author liuxuyang
	 */
	private Trans00605VO buildTrans00605VO(Long buyDtlId) {
		Trans00605VO transVo = new Trans00605VO();
        transVo.setCustCaBuyDtlId(buyDtlId);
        //企业客户数字证书回收
		transVo.setCustType("C"); 			
        transVo.setRmk("付费购买移动数字证书退款");
        transVo.setReqSource("wallet");
        return transVo;
	}

	
	@Override
	public void caCertToExpireNotify() {
		logger.error("开始处理移动数字证书即将过期提醒任务");
		String sql = buildSql();
		Map<String, Object> param = new HashMap<>(16);
		List<Object[]> list = commonQueryDAO.findBySql(sql, param);
		List<MobCertRemendVO> custList = buildList(list);
		if (custList != null && custList.size() > 0) {
			for (MobCertRemendVO custInfo : custList) {
				try { 
					if (!"999".equals(custInfo.getOperId() )) {
						logger.error("移动数字证书企业财务员发送手机号: " + custInfo.getOperMobile() +",userId: "+custInfo.getUserId());
	                    if (StringUtils.isNotEmpty(custInfo.getOperMobile())) {
	                    	sendSms(custInfo.getOperMobile(),custInfo.getValidTo() );
	                    }
					} else {
						if(StringUtils.isNotEmpty(custInfo.getCustPhone())) {
							logger.error("移动数字证书企业发送手机号为: " + custInfo.getCustPhone()+",userId: "+custInfo.getUserId());
		                    sendSms(custInfo.getCustPhone(), custInfo.getValidTo());
						} else if (StringUtils.isNotEmpty(custInfo.getCustEmail())) {
							// 企业邮箱不为空，发送邮件提醒
							logger.error("移动数字证书企业发送邮箱为: " + custInfo.getCustEmail()+",userId: "+custInfo.getUserId());
							sendMail(custInfo.getCustEmail(), custInfo.getValidTo());
						}
					}
				} catch (GopayException e) {
        			logger.error("短信提醒数字证书安装过期，发送失败！",e);
        		}
			}
		}
	}
	
	/**
	 * @Description: 发短信
	 * @param mobile
	 * @throws GopayException 
	 * @author liuxuyang
	 */
	private void sendSms(String mobile,Date limitDate) throws GopayException {
		String remidDdate = DateUtils.format(limitDate, "yyyy-MM-dd' 'HH:mm:ss");
		RequestVO req = new RequestVO();
		req.setAppId(SMSAppId._90000.value);
		req.setMobile(mobile);
		req.setContent("您有待安装的数字证书安装日期将于"+remidDdate+"过期，为确保您的交易安全及账户出款业务可以正常使用，请在到期日前登录国付宝账户进行安装。 ");
		ResponseVO resp = GopaySMSHttpClient.getInstance().createVerificationCode(req);
		if (resp != null) {
			if ("100".equals(resp.getStatusCode())) {
				logger.info("短信提醒数字证书安装过期，已经发送成功！");
			} else {
				logger.error("短信提醒数字证书安装过期，发送失败！，原因：" + resp.getMessage());
				throw new GopayException("","短信提醒数字证书安装过期，发送失败！，原因：" + resp.getMessage());
			}
		}
	}
	/**
	 * @Description: 发邮件
	 * @param email
	 * @param limitDate 
	 * @author liuxuyang
	 */
	private void sendMail(String email,Date limitDate) {
		String remidDdate = DateUtils.format(limitDate, "yyyy-MM-dd' 'HH:mm:ss");
        Send send = new Send();
        send.setReceivers(email.trim().toLowerCase());
        send.setTemplate("custMobCaCertRemind");
        send.setLevel(MailLevel.MID);
        Map<String, Object> json = new HashMap<String, Object>(16);
        json.put("replacethistotheLogoAddr", portalUrl);
        json.put("replacethistotheLimitDate", remidDdate);
        send.setContent(JSONObject.fromObject(json).toString());
        sendMail(send);
	}
	/**
	 * @Description: 执行发送邮件
	 * @param send 
	 * @author liuxuyang
	 */
	private void sendMail(Send send) {
		try {
			MailRemoteService mailRemoteService = this.getMailRemoteService();
			mailRemoteService.addMailTask(send);
		} catch (GopayException e) {
			logger.error("发送邮件异常：", e);
		}

	}
	/**
	 * @Description: 获取邮件服务
	 * @return
	 * @throws GopayException 
	 * @author liuxuyang
	 */
	private MailRemoteService getMailRemoteService() throws GopayException {
		MailRemoteService service = null;
		service = (MailRemoteService)RemoteServiceUtil.getRemoteService(MailRemoteService.class, notifyEmailUrl);
		return service;
	}
	
	/**
	 * @Description: 查询出还有一天就过期的购买流水
	 * @return 
	 * @author liuxuyang
	 */
	private String buildSql() {

        StringBuilder sql = new StringBuilder();
        
        sql.append("select info.cust_id, ");
        sql.append("info.cust_name, ");
        sql.append("info.email, ");
        sql.append("info.cust_type, ");
        sql.append("info.mob, ");
        sql.append("u.user_id, ");
        sql.append("u.oper_id,");
        sql.append("u.oper_name, ");
        sql.append("u.oper_mobile, ");
        sql.append("d.id, ");
        sql.append("d.install_expire_ts ");
        sql.append("from CUST_MOB_CA_BUY_DTL d ");
        sql.append("left join user_info u on d.cust_id = u.cust_id and u.oper_id = '999' ");
        sql.append("left join gopay_all_cust_info info on info.cust_id = d.cust_id  ");
        sql.append(" where d.install_expire_ts < sysdate + 1 ");
        sql.append(" and info.CUST_TYPE = '2' ");
        sql.append(" and d.unused_count > 0 ");
        
        sql.append("union all ");
        
        sql.append("select info.cust_id, ");
        sql.append("info.cust_name, ");
        sql.append("info.email, ");
        sql.append("info.cust_type, ");
        sql.append("info.mob, ");
        sql.append("u.user_id, ");
        sql.append("u.oper_id, ");
        sql.append("u.oper_name, ");
        sql.append("u.oper_mobile, ");
        sql.append("d.id, ");
        sql.append("d.install_expire_ts ");
        sql.append("from WA_CERTIFICATE_ALLOCATION r ");
        sql.append("left join CUST_MOB_CA_BUY_DTL d on r.cert_buy_id = d.id   ");
        sql.append("left join user_info u on r.treasurer_user_id = u.user_id  ");
        sql.append("left join gopay_all_cust_info info on info.cust_id = u.cust_id  ");
        sql.append("where r.allocation_status = '02'  ");
        sql.append("and info.CUST_TYPE = '2'  ");
        sql.append("and d.install_expire_ts < sysdate +1 ");
        
        logger.info("查询语句为：" + sql);
        
        return sql.toString();
	}
	
	/**
	 * @Description: 封装待提醒客户信息
	 * @param list
	 * @return 
	 * @author liuxuyang
	 */
	private List<MobCertRemendVO> buildList(List<Object[]> list) {
		List<MobCertRemendVO> custList = new ArrayList<MobCertRemendVO>();

		if (list != null && list.size() > 0) {
			MobCertRemendVO cust = null;
			for (Object[] arr : list) {
				cust = new MobCertRemendVO();
				if (arr[0] != null) {
					cust.setCustId(arr[0].toString());
				}

				if (arr[1] != null) {
					cust.setCustName(arr[1].toString());
				}

				if (arr[2] != null) {
					cust.setCustEmail(arr[2].toString());
				}

				if (arr[3] != null) {
					cust.setCustType(arr[3].toString());
				}

				if (arr[4] != null) {
					cust.setCustPhone(arr[4].toString());
				}

                if (arr[5] != null) {
                    cust.setUserId(arr[5].toString());
                }

                if (arr[6] != null) {
                    cust.setOperId(arr[6].toString());
                }
                
                if (arr[7] != null) {
                    cust.setOperName(arr[7].toString());
                }

                if (arr[8] != null) {
                    cust.setOperMobile(arr[8].toString());
                }
                
                if (arr[9] != null) {
                    cust.setId(arr[9].toString());
                }
                
                if (arr[10] != null) {
					cust.setValidTo((Date) arr[10]);
				}

				custList.add(cust);
			}
		}

		return custList;
	}
}
